# -*- coding: UTF-8 -*-

# Copyright (C) 2009 Daniele Favara.
# Written by Daniele Favara <daniele@zeroisp.com>.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

"""
Auth* related model.

This is where the models used by :mod:`repoze.who` and :mod:`repoze.what` are
defined.

It's perfectly fine to re-use this definition in the gavidocg application,
though.

"""
import os
from datetime import datetime
import sys
from tg import request
from sqlalchemy import *
from sqlalchemy import Table, ForeignKey, Column
from sqlalchemy.types import String, Unicode, UnicodeText, Integer, DateTime, \
                             Boolean, Float
from sqlalchemy.orm import relation, backref, synonym


from tgext.zeroisp.model import DeclarativeBase, metadata, DBSession
from tgext.zeroisp.model.auth import User
from tgext.zeroisp.model.types import *


user_team_table = Table('user_team', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('team_id', Integer, ForeignKey('team.team_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

keyword_post_table = Table('keyword_post', metadata,
    Column('keyword_id', Integer, ForeignKey('keyword.keyword_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('post_id', Integer, ForeignKey('post.post_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)


class Language(DeclarativeBase):
    __tablename__ = 'language'
    id = Column(Integer, primary_key=True)
    lang_code = Column(String(2), nullable=False, unique=True, index=True)
    lang_name = Column(Unicode(30))
    #picasa_user = Column(Unicode(255),nullable=False)

class Album(DeclarativeBase):
    __tablename__ = 'album'
    id = Column(Integer, primary_key=True)
    album_id = Column(Unicode(255), nullable=False)
    album_thumb = Column(Unicode(255),nullable=False)
    #picasa_user = Column(Unicode(255),nullable=False)

    @property
    def feed(self):
        from tgext.zeroisp.lib.utils import get_picasa
        p = get_picasa()
        return p.get_album(str(self.album_id))

class GoogleAddress(DeclarativeBase):
    __tablename__ = 'google_address'
    google_address_id = Column(Integer, primary_key=True)
    accuracy = Column(Integer, nullable=False)
    google_lat = Column(Float, nullable=False)
    google_lng = Column(Float, nullable=False)
    country_name_code = Column(Unicode(2))
    country_name = Column(Unicode(255))
    administrative_area_name = Column(Unicode(255))
    sub_administrative_area_name = Column(Unicode(255))
    locality_name = Column(Unicode(255))
    dependent_locality_name =  Column(Unicode(255))
    thoroughfare_name = Column(Unicode(255))
    google_address = Column(Unicode(255))
    postal_code_number = Column(Unicode(255))

class Team(DeclarativeBase):
    """The team definition."""
    __tablename__ = 'team'

    #{ Columns

    team_id = Column(Integer, autoincrement=True, primary_key=True)

    team_alias = Column(Unicode(128), unique=True, nullable=False)

    team_parent_id = Column(Integer,ForeignKey('team.team_id'))

    team_membership = Column(Enum(('public', 'invitation')), default='public',
                        nullable=False)

    team_language_support = Column(Enum(('defined', 'all')), default='defined',
                              nullable=False)

    team_display_name = Column(Unicode(255))

    team_created = Column(DateTime, default=datetime.now)

    #{ Relations

    users = relation('User', secondary=user_team_table, backref='teams')

    subteams = []

    forum = None

    languages = []

    #@property
    #def posts(self):
    #    categories = [x.category_id for x in self.categories]
    #    return DBSession.query(Post).filter(
    #                        Post.post_category_id.in_(categories)).all()

    @property
    def categories(self):
        return DBSession.query(Category).all()

    @property
    def page(self):
        try:
            return self.teampage[0].post
        except:
            return None

    @property
    def title(self):
        try:
            return self.teampage[0].post.post_title
        except:
            return None

    @property
    def mail(self):
        try:
            return self.company[0].mail
        except:
            None

    @property
    def phone(self):
        try:
            return self.company[0].phone
        except:
            None

    @property
    def fax(self):
        try:
            return self.company[0].fax
        except:
            None

    @property
    def vat_code(self):
        try:
            return self.company[0].vat_code
        except:
            None
    @property
    def fiscal_code(self):
        try:
            return self.company[0].fiscal_code
        except:
            None

    @property
    def description(self):
        try:
            return self.teampage[0].post.post_description
        except:
            return None

    @property
    def xml_content(self):
        try:
            return "<div>%s</div>"%self.teampage[0].post.post_content
        except:
            return None

    @property
    def address(self):
        try:
            return self.teampage[0].post.post_address
        except:
            return None

    @property
    def user_role(self):
        user= DBSession.query(User).filter_by(user_name=request.identity['repoze.who.userid']).one()
        try:
            return DBSession.query(Membership).filter_by(user_id=user.user_id, team_id=self.team_id).one().role
        except:
            return None

    def role(self, user_id):
        try:
            return DBSession.query(Membership).filter_by(user_id=user_id, team_id=self.team_id).one().role
        except:
            return None

    def users_by_role(self, urole):
        try:
            mships = DBSession.query(Membership).filter_by(team_id=self.team_id, role=urole).all()
            return [x.user for x in mships]
        except:
            return None

    def __repr__(self):
        return '<Team: name=%s>' % self.team_alias

    def __unicode__(self):
        return self.team_alias

    #}

Team.parent_team = relation('Team', remote_side=Team.team_id,
                           backref=backref('subteams',
                                           order_by=Team.team_alias))

class TeamPage(DeclarativeBase):
    """Represent the settings for the membership of a user in a given team."""
    __tablename__ = 'teampage'
    teampage_id = Column(Integer, autoincrement=True, primary_key=True)
    post_id = Column(Integer,
                        ForeignKey('post.post_id'))

    team_id = Column(Integer,
                         ForeignKey('team.team_id'))

    post = relation('Post', backref="teampage")
    team = relation('Team', backref="teampage")

class Company(DeclarativeBase):
    """Represent the settings for the membership of a user in a given team."""
    __tablename__ = 'company'
    company_id = Column(Integer, autoincrement=True, primary_key=True)
    vat_code = Column(Unicode(16))
    fiscal_code = Column(Unicode(16))
    phone = Column(Unicode(16))
    fax = Column(Unicode(16))
    mail = Column(Unicode(255))
    team_id = Column(Integer,
                         ForeignKey('team.team_id'))
    teams = relation('Team', backref="company")

class Membership(DeclarativeBase):
    """Represent the settings for the membership of a user in a given team."""
    __tablename__ = 'membership'

    #{ Fields

    membership_id = Column(Integer, autoincrement=True, primary_key=True)

    user_id = Column(Integer,
                        ForeignKey('user.user_id'))

    team_id = Column(Integer,
                         ForeignKey('team.team_id'))

    role = Column(Enum(('manager', 'user', 'supervisor', 'admin')), default='user')

    send_announcements = Column(Boolean(), default=True)

    send_forum_threads = Column(Boolean(), default=False)

    send_vacancies = Column(Boolean(), default=True)

    send_event_invitation = Column(Boolean(), default=True)

    #{ Relations

    user = relation(User, backref='membership')


    team = relation(Team, backref='member')


    def __init__(self, team, user):
        """Add C{member} to C{team}"""
        self.team = team
        self.user = user

class Category(DeclarativeBase):

    __tablename__ = 'category'

    category_id = Column(Integer, primary_key=True)

    category_name = Column(Unicode(16), unique=True)

    category_label = Column(Unicode(255), nullable=False)

    category_hasaddress = Column(Boolean, default=False)

    category_titleisaddress = Column(Boolean, default=False)

    category_hasphotos = Column(Boolean,default=False)

    category_isevent = Column(Boolean,default=False)

    category_hasvideo = Column(Boolean,default=False)

    category_description = Column(Unicode(255), nullable=True)

    @property
    def short_description(self):
        if self.category_description:
            if len(self.category_description) > 40:
                return self.category_description[0:40]
            else:
                return self.category_description
        else:
            return "No description available"

class Detail(DeclarativeBase):
    __tablename__ = 'detail'
    detail_id = Column(Integer, primary_key=True)
    detail_alias = Column(Unicode(128), unique=True, nullable=False)
    detail_category_id = Column(Integer, ForeignKey('category.category_id'))
    detail_category = relation('Category', backref='details')

class FieldKey(DeclarativeBase):

    __tablename__ = 'fieldkey'

    fieldkey_id = Column(Integer, primary_key=True)

    fieldkey_order = Column(Integer)

    #fieldkey_category_id = Column(Integer, ForeignKey('category.category_id'))

    #fieldkey_category = relation('Category', backref='fieldkeys')

    fieldkey_detail_id = Column(Integer, ForeignKey('detail.detail_id'))

    fieldkey_detail = relation('Detail', backref='fieldkeys')

    fieldkey_type = Column(Unicode(255), nullable=False)

    fieldkey_name = Column(Unicode(255))

    fieldkey_value = Column(Unicode(255),nullable=False)

    fieldkey_label = Column(Unicode(255),nullable=False)

    fieldkey_validate = Column(Unicode(255))

class FieldVal(DeclarativeBase):

    __tablename__ = 'fieldval'

    fieldval_id = Column(Integer, primary_key=True)

    fieldval_content = Column(Unicode(255), nullable=False)

    fieldval_key_id = Column(Integer, ForeignKey('fieldkey.fieldkey_id'))

    fieldval_key = relation('FieldKey', backref='values')

    fieldval_post_id =  Column(Integer, ForeignKey('post.post_id'))

    fieldval_post = relation('Post', backref='values')


class Post(DeclarativeBase):
    __tablename__ = 'post'
    post_id = Column(Integer, primary_key=True)
    post_date = Column(DateTime, default=datetime.now)
    post_content = Column(UnicodeText)
    post_description = Column(Unicode(255),nullable=False)
    post_title = Column(Unicode(255),nullable=False)
    post_modified = Column(DateTime, default=datetime.now)
    post_published = Column(Boolean, default=False)
    post_address_id =  Column(Integer, ForeignKey('google_address.google_address_id'))
    post_address = relation('GoogleAddress', backref='posts')
    post_album_id = Column(Unicode(255), ForeignKey('album.album_id'))
    post_album = relation('Album', backref='posts')
    #post_team_id = Column(Integer, ForeignKey('team.team_id'))
    #post_team = relation('Team', backref='posts')
    post_category_id =  Column(Integer, ForeignKey('category.category_id'))
    post_category = relation('Category', backref='posts')
    post_event_from =  Column(DateTime, default=datetime.now)
    post_event_to =  Column(DateTime, default=datetime.now)
    post_parent = Column('post_parent', Integer,
                           ForeignKey('post.post_id'))
    post_team_id = Column(Integer,
                         ForeignKey('team.team_id'))

    post_team = relation('Team', backref='posts')

    keywords = relation('Keyword', secondary=keyword_post_table, backref='posts')

    @property
    def xml_content(self):
        if self.post_content:
            return "<div>%s</div>"%(self.post_content)

    @property
    def short_description(self):
        if self.post_description:
            if len(self.post_description) > 100:
                return self.post_description[0:100]
            else:
                return self.post_description
        else:
            return "No description available"

    def ordered_values(self, detail_id):
        sel = select([FieldKey.fieldkey_name, FieldKey.fieldkey_label, FieldVal.fieldval_content]).\
                where(and_(FieldVal.fieldval_post_id==Post.post_id,
                           Post.post_id==self.post_id,
                           FieldVal.fieldval_key_id==FieldKey.fieldkey_id,
                           FieldKey.fieldkey_detail_id==detail_id)).\
                order_by(FieldKey.fieldkey_order)
        return DBSession.query(sel).all()

Post.parent = relation('Post', remote_side=Post.post_id,
                           backref=backref('childs',
                                           order_by=Post.post_modified))



class Keyword(DeclarativeBase):

    __tablename__ = 'keyword'

    keyword_id = Column(Integer, primary_key=True)
    keyword = Column(Unicode(16), nullable=False, unique=True)

